/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.evangelion.core.utils;

import com.evangelion.core.bean.Meses;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.write.*;

/**
 *
 * @author Kellycasma
 */
public class ManagerExcelReport {

    public static File imprimir(Meses meses) {
        File f = null;
        try {
            WritableFont fontNormal = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD);

            WritableCellFormat cellFormatNumber = new WritableCellFormat(fontNormal);
            cellFormatNumber.setAlignment(Alignment.CENTRE);

            WritableCellFormat cellFormatNormal = new WritableCellFormat(fontNormal);
            cellFormatNormal.setAlignment(Alignment.RIGHT);
            cellFormatNormal.setWrap(true);
            cellFormatNormal.setBorder(Border.ALL, BorderLineStyle.NONE);

            WritableCellFormat cellFormatCenter = new WritableCellFormat(fontNormal);
            cellFormatCenter.setAlignment(Alignment.CENTRE);
            cellFormatCenter.setWrap(true);
            cellFormatCenter.setBorder(Border.ALL, BorderLineStyle.NONE);

            WritableCellFormat cellFormatIzq = new WritableCellFormat(fontNormal);
            cellFormatIzq.setAlignment(Alignment.LEFT);
            cellFormatIzq.setWrap(true);
            cellFormatIzq.setBorder(Border.ALL, BorderLineStyle.NONE);

            StringBuilder sb = new StringBuilder();
            sb.append("c:\\archi\\");
            sb.append(meses.getA());
            sb.append(".xls");
//            f = new File(sb.toString());
            f = new File(sb.toString());
            WritableWorkbook workbook = Workbook.createWorkbook(f);
            WritableSheet hoja1 = workbook.createSheet(meses.getA(), 0);

           hoja1.setPageSetup(PageOrientation.PORTRAIT, PaperSize.A4, 0.5, 0.5);
//            hoja1.getSettings().setTopMargin(2.5);
            hoja1.getSettings().setLeftMargin(0.1);
            hoja1.getSettings().setRightMargin(0.1);
//            hoja1.getSettings().setBottomMargin(2.5);


            hoja1.setColumnView(0, 3);
            hoja1.setColumnView(1, 17);
            hoja1.setColumnView(2, 8);
            hoja1.setColumnView(3, 13);
            hoja1.setColumnView(4, 10);
            hoja1.setColumnView(5, 8);
            hoja1.setColumnView(6, 13);
            hoja1.setColumnView(7, 12);
            hoja1.setColumnView(8, 12);

            hoja1.setRowView(1, 580);
            hoja1.setRowView(7, 315);
            hoja1.setRowView(15, 60);

            hoja1.mergeCells(2, 2, 6, 2);
            hoja1.mergeCells(2, 3, 6, 3);
            hoja1.mergeCells(2, 4, 6, 4);
            hoja1.mergeCells(2, 5, 6, 5);

            hoja1.addCell(getLabel(1, 8, "Inscripción", cellFormatIzq));
            hoja1.addCell(getLabel(1, 9, "Clases circulación", cellFormatIzq));
            hoja1.addCell(getLabel(1, 10, "Tramitación", cellFormatIzq));
            hoja1.addCell(getLabel(1, 11, "Examen circulación", cellFormatIzq));
            hoja1.addCell(getLabel(1, 12, "Otros gastos", cellFormatIzq));

            hoja1.addCell(getLabel(2, 2, meses.getO(), cellFormatIzq));
            hoja1.addCell(getLabel(2, 3, meses.getC(), cellFormatIzq));
            hoja1.addCell(getLabel(2, 4, meses.getL(), cellFormatIzq));
            hoja1.addCell(getLabel(2, 5, meses.getMadrid(), cellFormatIzq));

            hoja1.addCell(getLabel(8, 3, meses.getA(), cellFormatCenter));
            double G9 = meses.getT() + meses.getX()
                    + meses.getAl() + meses.getAn() + meses.getAv() + meses.getAx()
                    + meses.getBc() + meses.getBe() + meses.getBg()
                    + meses.getAe() + meses.getAi()
                    + meses.getAm() + meses.getAz()
                    + meses.getBf() + meses.getBi() + meses.getBh();

            hoja1.addCell(getLabel(6, 8, G9 + " €", cellFormatNormal));

            double G10 = meses.getR() + meses.getZ() + meses.getAd()
                    + meses.getAj() + meses.getAp() + meses.getAa() + meses.getAg()
                    + meses.getAq() + meses.getAs()+ meses.getU();
            hoja1.addCell(getLabel(6, 9, G10 + " €", cellFormatNormal));

            double G11 = meses.getBa() + meses.getS();
            hoja1.addCell(getLabel(6, 10, G11 + " €", cellFormatNormal));
            double G12 = meses.getAb() + meses.getAh() + meses.getAr()
                    + meses.getAt() + meses.getV() + meses.getW() + meses.getY()
                    + meses.getAc() + meses.getAk() + meses.getAo()
                    + meses.getAu() + meses.getAw() + meses.getBb()
                    + meses.getBd();
            hoja1.addCell(getLabel(6, 11, G12 + " €", cellFormatNormal));
            double G13 = meses.getAf();
            hoja1.addCell(getLabel(6, 12, meses.getAf() + " €", cellFormatNormal));

            //PORCENTAJES IGUALES
            hoja1.addCell(getLabel(5, 8, meses.getH(), cellFormatNormal));
            hoja1.addCell(getLabel(5, 9, meses.getH(), cellFormatNormal));
            hoja1.addCell(getLabel(5, 10, meses.getH(), cellFormatNormal));
            hoja1.addCell(getLabel(5, 11, meses.getH(), cellFormatNormal));
            hoja1.addCell(getLabel(5, 12, meses.getH(), cellFormatNormal));
            double porcentaje1 = 0;
            if (meses.getH().trim().equals("16%")) {
                porcentaje1 = 1.16;
             
            //PORCENTAJES IGUALES
            hoja1.addCell(getLabel(4, 8, BigDecimal.valueOf(G9 -(G9 / porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 9, BigDecimal.valueOf(G10 -(G10 / porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 10, BigDecimal.valueOf(G11 -(G11 / porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 11, BigDecimal.valueOf(G12 -(G12 / porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 12, BigDecimal.valueOf(G13 -(G13 / porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));

            //PORCENTAJES IGUALES
            hoja1.addCell(getLabel(3, 8, BigDecimal.valueOf(G9 / porcentaje1).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 9, BigDecimal.valueOf(G10 / porcentaje1).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 10, BigDecimal.valueOf(G11 / porcentaje1).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 11, BigDecimal.valueOf(G12 / porcentaje1).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 12, BigDecimal.valueOf(G13 / porcentaje1).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));

            }else{
            //PORCENTAJES IGUALES
            hoja1.addCell(getLabel(4, 8,"0.00 €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 9, "0.00 €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 10, "0.00 €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 11, "0.00 €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 12,"0.00 €", cellFormatNormal));

            //PORCENTAJES IGUALES
            hoja1.addCell(getLabel(3, 8, BigDecimal.valueOf(G9).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 9, BigDecimal.valueOf(G10).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 10, BigDecimal.valueOf(G11).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 11, BigDecimal.valueOf(G12).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(3, 12, BigDecimal.valueOf(G13).setScale(2, RoundingMode.HALF_DOWN) + " €", cellFormatNormal));

            }
            
            hoja1.addCell(getLabel(1, 21, meses.getB(), cellFormatCenter));
//            hoja1.addCell(getLabel(3, 21,  BigDecimal.valueOf((G9 * porcentaje2) + (G10 * porcentaje2) + (G11 * porcentaje2) + (G12 * porcentaje2) + (G13 * porcentaje2)).setScale(2, RoundingMode.HALF_DOWN) + "", cellFormatNormal));
//            hoja1.addCell(getLabel(4, 21,  BigDecimal.valueOf((G9 * porcentaje1) + (G10 * porcentaje1) + (G11 * porcentaje1) + (G12 * porcentaje1) + (G13 * porcentaje1)).setScale(2, RoundingMode.HALF_DOWN) + "", cellFormatNormal));
            hoja1.addCell(getLabel(3, 21, (meses.getG()) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(4, 21, (meses.getI()) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(6, 21, (meses.getJ()) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(8, 21, (meses.getJ()) + " €", cellFormatNormal));
            hoja1.addCell(getLabel(7, 21, (" -  €"), cellFormatNormal));
//            hoja1.addCell(getLabel(6, 21, (G9 + G10 + G11 + G12 + G13) + "", cellFormatNormal));
//            hoja1.addCell(getLabel(8, 21, (G9 + G10 + G11 + G12 + G13) + "", cellFormatNormal));


            workbook.write();
            workbook.close();

        } catch (Exception e) {
            System.out.println("error::::::::::::::::::::::::::" + e);
        }
        return f;
    }

    private static Label getLabel(int col, int row, String text, WritableCellFormat cellFormat) {
        return new Label(col, row, text, cellFormat);
    }

    private static jxl.write.DateTime getDate(int col, int row, java.sql.Date fecha, WritableCellFormat cellFormat) {
        return new jxl.write.DateTime(col, row, fecha, cellFormat);
    }

    public static byte[] getBytes(File f) {
        byte[] bytes = null;

        try {
            FileInputStream in = new FileInputStream(f);
            BufferedInputStream reader = new BufferedInputStream(in);

            int length = reader.available();

            bytes = new byte[length];

            reader.read(bytes, 0, length);
            reader.close();
        } catch (Exception e) {
            //e.printStackTrace();
        }
        return bytes;
    }
}
